package com.isb.emailclient.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteHelper extends SQLiteOpenHelper {

	public static final String TABLE_ACCOUNT = "tbl_app_accounts";
	public static final String TABLE_FOLDER = "tbl_folders";
	public static final String TABLE_MAIL = "tbl_mails";
	public static final String TABLE_ATTACHMENT = "tbl_attachment";
	
	public static final String DATABASE_NAME = "isb_clientmail.db";
	public static final int DATABASE_VERSION = 2;
	
	public static final String KEY_ID = "id";
	
	// columns for accounts table
	public static final String EMAIL = "email";
	public static final String PASSWORD = "passwords";

	// columns for folders table
	public static final String FOLDER_NAME = "folder";
	public static final String ACCOUNT_ID = "account_id";
	
	// columns for mails table
	public static final String MAIL_NUMBER = "mail_number";
	public static final String FOLDER_ID = "folder_id";
	public static final String FROM_ADDR = "from_address";
	public static final String TO_ADDR = "to_address";
	public static final String SUBJECT = "subject";
	public static final String CONTENT = "content";
	public static final String DATE_TIME = "date_time";
	
	// columns for attachments table
	public static final String MAIL_ID = "mail_id";
	public static final String FILE_NAME = "file_name";
	
	
	// create accounts table
	private static final String CREATE_ACCOUNT_TABLE = 
			"CREATE TABLE " + TABLE_ACCOUNT + "("
			+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ EMAIL + " TEXT, "
			+ PASSWORD + " TEXT)";
	
		// create folders table
		private static final String CREATE_FOLDER_TABLE = 
				"CREATE TABLE " + TABLE_FOLDER + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ FOLDER_NAME + " TEXT, "
				+ ACCOUNT_ID + " INTEGER)";
		
		// create mails table
		private static final String CREATE_MAIL_TABLE = 
				"CREATE TABLE " + TABLE_MAIL + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ MAIL_NUMBER + " INTEGER, "
				+ FOLDER_ID + " INTEGER, "
				+ FROM_ADDR + " TEXT, "
				+ TO_ADDR + " TEXT, "
				+ SUBJECT + " TEXT, "
				+ CONTENT + " TEXT, "
				+ DATE_TIME + " DATETIME)";
		
		
		// create accounts table
		private static final String CREATE_ATTACHMENT_TABLE = 
				"CREATE TABLE " + TABLE_ATTACHMENT + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ MAIL_ID + " INTEGER, "
				+ FILE_NAME + " TEXT)";
	
	
	public SQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// create table
		db.execSQL(CREATE_ACCOUNT_TABLE);
		db.execSQL(CREATE_FOLDER_TABLE);
		db.execSQL(CREATE_MAIL_TABLE);
		db.execSQL(CREATE_ATTACHMENT_TABLE);
	}
	
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		// on upgrade drop older tables
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACCOUNT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FOLDER);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_MAIL);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ATTACHMENT);
		
		// on create db
		onCreate(db);
	}
	
}
